#! /bin/bash
last_month_date=$(date -d "-1 month" "+%Y-%m-01")
last_month=$(date -d "-1 month" "+%Y-%m")
last_year=$(date -d "-1 month" "+%Y")
today=$(date -d "now" "+%Y-%m-%d")
if [[ $1 == "" ]]
then
yesterday=$(date -d "-1 day" "+%Y-%m-%d")
else
yesterday=$1
fi

hive -S -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;
--矢量化查询
set hive.vectorized.execution.enabled=true;
--关联优化器
set hive.optimize.correlation=true;
--读取零拷贝
set hive.exec.orc.zerocopy=true;
--join数据倾斜
set hive.optimize.skewjoin=true;
-- set hive.skewjoin.key=100000;
set hive.optimize.skewjoin.compiletime=true;
set hive.optimize.union.remove=true;
-- group倾斜
set hive.groupby.skewindata=true;

--校区、学科组合分组
--小时
INSERT overwrite itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    dwm.itcast_school_id,
    dwm.itcast_school_name,
    dwm.itcast_subject_id,
    dwm.itcast_subject_name,
    '-1' as tdepart_id,
    '-1' tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    dwm.payment_time_hour,
    '1' grouptype, 
    '1' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where concat_ws('-',payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= "${last_month_date}"
GROUP BY dwm.origin_type_stat,
    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day, dwm.payment_time_hour,
    dwm.itcast_school_id, dwm.itcast_school_name, dwm.itcast_subject_id, dwm.itcast_subject_name;

    --tian
INSERT overwrite itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    dwm.itcast_school_id,
    dwm.itcast_school_name,
    dwm.itcast_subject_id,
    dwm.itcast_subject_name,
    '-1' as tdepart_id,
    '-1' tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    '-1' dwm.payment_time_hour,
    '1' grouptype, 
    '2' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where concat_ws('-',payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= "${last_month_date}"
GROUP BY dwm.origin_type_stat,
    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day,
    dwm.itcast_school_id, dwm.itcast_school_name, dwm.itcast_subject_id, dwm.itcast_subject_name;

     --yue
INSERT overwrite itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    dwm.itcast_school_id,
    dwm.itcast_school_name,
    dwm.itcast_subject_id,
    dwm.itcast_subject_name,
    '-1' as tdepart_id,
    '-1' tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    '-1' dwm.payment_time_hour,
    '1' grouptype, 
    '4' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    '-1' dwm.payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where concat_ws('-',payment_time_year,dwm.payment_time_month) >= "${last_month}"
GROUP BY dwm.origin_type_stat,
    dwm.payment_time_year, dwm.payment_time_month,
    dwm.itcast_school_id, dwm.itcast_school_name, dwm.itcast_subject_id, dwm.itcast_subject_name;

    --nian
INSERT overwrite itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    count(dwm.customer_id) as signup_num,
    dwm.itcast_school_id,
    dwm.itcast_school_name,
    dwm.itcast_subject_id,
    dwm.itcast_subject_name,
    '-1' as tdepart_id,
    '-1' tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    '-1' dwm.payment_time_hour,
    '1' grouptype, 
    '5' as time_type,
    dwm.payment_time_year,
    '-1' dwm.payment_time_month,
    '-1' dwm.payment_time_day
from itcast_dwm.customer_signup_dwm dwm
where payment_time_year >= "${last_year}"
GROUP BY dwm.origin_type_stat,
    dwm.payment_time_year,
    dwm.itcast_school_id, dwm.itcast_school_name, dwm.itcast_subject_id, dwm.itcast_subject_name;
"